/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package proveedornextbus;
import java.net.*;
import java.io.*;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 *
 * @author dani
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws Exception
    {

        Connection c = null;

          try {
            // The second and third arguments are the username and password,
            // respectively. They should be whatever is necessary to connect
            // to the database.
            c = DriverManager.getConnection("jdbc:postgresql://localhost/sistemaprediccion",
                                            "postgres", "postgres");
          } catch (SQLException se) {
            System.out.println("Couldn't connect: print out a stack trace and exit.");
            se.printStackTrace();
            System.exit(1);
          }

          if (c != null)
            System.out.println("Conexion exitosa");
          else
            System.out.println("No se pudo conectar a la BD");

        long lastTime = 0;
        while(true)
        {
            Thread.sleep(15000);
            String xml = obtenerPosiciones(lastTime);
            lastTime = guardarEnBD(xml,c);
        }
    }

    private static String obtenerPosiciones(long lastTime) throws Exception
    {
        URL nextbus = new URL("http://webservices.nextbus.com/service/publicXMLFeed?command=vehicleLocations&a=sf-muni&r=1&t="+lastTime);
        URLConnection yc = nextbus.openConnection();
        BufferedReader in = new BufferedReader(
                                new InputStreamReader(
                                yc.getInputStream()));
        String inputLine;
        String resultado = "";

        while ((inputLine = in.readLine()) != null)
            resultado = resultado + inputLine;
        in.close();

        return resultado;
    }

    private static long guardarEnBD(String xml, Connection c)
    {

        String idVehiculo,routeTag, dirTag, latitud, longitud,
                secsSinceLastReport, predictable,
                heading, speedKmh, leadingVehicle;

        String[] partes = xml.split("lastTime");

        String[] posiciones = partes[0].split("<vehicle");

        String[] fecha = partes[1].split("\"");

        if(posiciones.length > 1)
        {
            for(int i = 1; i < posiciones.length; i++)
                guardarRegistro(posiciones[i], c, fecha[1]);
        }

        Date hora = new java.util.Date();
        SimpleDateFormat formatter = new SimpleDateFormat("EEE, dd-MMM-yyyy HH:mm:ss");

        String formattedDate = formatter.format(hora);
        System.out.println(posiciones.length+" registros guardados" + hora);
        return Long.valueOf(fecha[1]);

    }

    private static void guardarRegistro(String reg, Connection c, String fechaConsulta)
    {
        String[] campos = reg.split("\"");

        Statement s = null;
        try {
          s = c.createStatement();
        } catch (SQLException se) {
          System.out.println("Error en la BD");
          se.printStackTrace();
          System.exit(1);
        }

        if(campos[1].charAt(0)!='0' && campos[1].charAt(0)!='1' && campos[1].charAt(0)!='2' && campos[1].charAt(0)!='3' &&
                campos[1].charAt(0)!='4' && campos[1].charAt(0)!='5' && campos[1].charAt(0)!='6' && campos[1].charAt(0)!='7' &&
                campos[1].charAt(0)!='8' && campos[1].charAt(0)!='9')
            campos[1] = (String) campos[1].subSequence(1, 4);

        String consulta;
        if(campos[4].compareTo(" dirTag=") == 0)
        {
        consulta = "INSERT INTO motorprediccion.posiciones_crudo2("+
            "\"vehicleId\", \"routeTag\", \"dirTag\", \"latitud\", \"longitud\", \"secsSinceLastReport\", "+
            "\"predictable\", \"heading\", \"speedKmh\", \"fechaConsulta\")"+
            "VALUES ("+
            campos[1]+",'"+
            campos[3]+"','"+
            campos[5]+"',"+
            campos[7]+","+
            campos[9]+","+
            campos[11]+",'"+
            campos[13]+"',"+
            campos[15]+","+
            campos[17]+","+ fechaConsulta +");";
        }
        else
        {
            consulta = "INSERT INTO motorprediccion.posiciones_crudo2("+
            "\"vehicleId\", \"routeTag\", \"latitud\", \"longitud\", \"secsSinceLastReport\", "+
            "\"predictable\", \"heading\", \"speedKmh\", \"fechaConsulta\")"+
            "VALUES ("+
            campos[1]+",'"+
            campos[3]+"','"+
            campos[5]+"',"+
            campos[7]+","+
            campos[9]+","+
            campos[11]+","+
            campos[13]+",'"+
            campos[15]+"',"+ fechaConsulta +");";
        }
       
        try {
          s.executeUpdate(consulta);
        } catch (SQLException se) {
          System.out.println("SQL Error");
          se.printStackTrace();
          System.exit(1);
        }
        }
}
